/**
 * Description: StudentDAOImpl.java
 * Copyright (c) 2008-2009  Neo. 
 * All Rights Reserved.
 * @version 1.0  Mar 24, 2009 1:03:44 PM 李萌（neolimeng@gmail.com）created
 */
package org.paradise.dms.dao.impl;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import org.apache.commons.lang.builder.ReflectionToStringBuilder;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.paradise.dms.pojo.CollegeInfo;
import org.paradise.dms.pojo.MajorInfo;
import org.paradise.dms.pojo.Student;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

import com.dheaven.framework.dao.BaseHibernateDao;
import com.dheaven.framework.dao.DaoException;

/**
 * Description: Copyright (c) 2008-2009 Neo. All Rights Reserved.
 * 
 * @version 1.0 Mar 24, 2009 1:03:44 PM 李萌（neolimeng@gmail.com）created
 */
@Service
public class StudentDAOImpl extends BaseHibernateDao<Student> {
	private static Logger log = Logger.getLogger(StudentDAOImpl.class);

	/**
	 * 
	 * Description: 增加一条学生记录信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:15:58 PM 李萌（neolimeng@gmail.com）创建
	 * @param student
	 * @throws DaoException
	 */
	public void insertStudent(Student student) throws DaoException {
		this.create(student);
		log.info("成功插入学生,学号为：：：：：：："+student.getStudentno());
	}
	
	public int batchInsert(List<Student> list) {

		Session session = null;
		Transaction tx = null;
		try {
			session = this.getHibernateTemplate().getSessionFactory()
					.openSession();
			tx = session.beginTransaction();
			for (int i = 0; i < list.size(); i++) {
				session.save(list.get(i));
				if (i % 500 == 0) {
					session.flush();
					session.clear();
				}
			}
			tx.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			return -2;
		} finally {
			if (session != null)
				session.close();
		}
		return 1;

	}
	
	/**
	 * 取得所有学生的学号，以供在导入学生时验证。
	 * @return
	 */
	public HashSet<String> getStudentNo() {
		String queryString = "select studentno from Student a where a.studentind=1";
		List list = this.getHibernateTemplate().find(queryString);
		if(null!=list && list.size()>0) {
			HashSet<String> set = new HashSet<String>();
			for(int i=0;i<list.size();i++) {
				set.add((String)list.get(i));
			}
			return set;
		}
		return null;
	}

	/**
	 * 
	 * Description:更新一条学生记录信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:16:22 PM 李萌（neolimeng@gmail.com）创建
	 * @param student
	 * @throws DaoException
	 */
	public void updateStudent(Student student) throws DaoException {
		this.update(student);
		log.info("DMS_info：" + "更新一条学生记录"
				+ ReflectionToStringBuilder.toString(student).toString()
				+ "成功!");
	}

	/**
	 * 
	 * Description: 删除一条学生记录信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:16:38 PM 李萌（neolimeng@gmail.com）创建 changed by
	 *             郑旭,假删除
	 * @param studentid
	 *            学生ID（注意：不是学号！！）
	 * @throws DaoException
	 */
	public void deleteStudent(int studentid) {
		Student student = (Student) this.find(
				"from Student where studentid = " + studentid).get(0);
		student.setStudentind(0);
		try {
			this.update(student);
			log.info("DMS_info：" + "删除一条id为" + studentid + "的学生记录成功!");
		} catch (DaoException e) {
			log.error("DMS_error:删除一条id为" + studentid + "的学生记录出错");
			log.error("错误原因:" + e);
		}

	}

	/**
	 * 
	 * Description: 通过学生ID取得学生记录信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:17:15 PM 李萌（neolimeng@gmail.com）创建
	 * @param studentid
	 *            学生ID（注意：不是学号！！）
	 * @return
	 * @throws DaoException
	 */
	public Student getStudent(int studentid) {
		try {
			return (Student) this.find(
					"from Student where studentid = ? and studentind = 1",
					new Object[] { studentid }).get(0);
		} catch (DaoException e) {
			log.error("DMS_error:通过学生ID取得学生记录信息");
			log.error("错误原因:" + e);
		}
		return null;
	}

	/**
	 * 
	 * Description: 根据学生学号取得学生记录信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:19:09 PM 李萌（neolimeng@gmail.com）创建
	 * @param studentno
	 *            学号
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List getStudentByStudentNo(final String studentno)
			throws DaoException {
		// 还要查出学生的 专业和学院等信息呢。
		// return this.find("from Student where studentno = ? and studentind =
		// 1", new Object[] { studentno });
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				String sql = "select student.studentid,student.studentno,studentname,studentgender,studentpreasignedid,";
				sql += "collegeinfo.collegeinfoname,majorinfo.majorinfoname,studentclass,";
				sql += "dormitory.dormitorydisplayname,studenttelephone,studentpicturepath,studentethnic,";
				sql += "studentbirthplace,studentbirthday,studenttype from student ";
				sql += "left join collegeinfo on student.collegeinfoid = collegeinfo.collegeinfoid ";
				sql += "left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				sql += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				sql += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid ";
				sql += "where student.studentno = '" + studentno + "'";
				Query query = s.createSQLQuery(sql);
				return query.list();
			}
		});

	}

	/**
	 * 
	 * Description: for学生基本信息的模糊查询
	 * 
	 * @Version1.0 Apr 27, 2009 1:21:39 AM 李双江（paradise.lsj@gmail.com）创建
	 * @param studentno
	 * @return
	 * @throws DaoException
	 */

	@SuppressWarnings("unchecked")
	public List<Student> getStudentByStudentNoForDimStudentInfoSearch(
			String studentno) {
		try {
			return this.find(
					"from Student where studentno = ? and studentind = 1",
					new Object[] { studentno });
		} catch (DaoException e) {
			log.error("DMS_error:学生基本信息的模糊查询出错！");
			log.error("错误原因:" + e);
		}
		return null;
	}

	/**
	 * 
	 * Description: 根据学院ID值取得该学院学生信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:26:01 PM 李萌（neolimeng@gmail.com）创建
	 * @param collegeinfoid
	 *            学院ID值
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List<Student> getStudentsByCollegeInfoId(int collegeinfoid) {
		try {
			return (List<Student>) this.find(
					"from Student where collegeinfoid = ? and studentind = 1",
					new Object[] { collegeinfoid });
		} catch (DaoException e) {
			log.error("DMS_error:根据学院ID值取得该学院学生信息出错！");
			log.error("错误原因:" + e);
		}
		return null;
	}

	/**
	 * 
	 * Description: 根据专业ID取得该专业学生信息
	 * 
	 * @Version1.0 Mar 24, 2009 1:28:50 PM 李萌（neolimeng@gmail.com）创建
	 * @param majorinfoid
	 *            专业ID值
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List<Student> getStudentsByMajorInfoId(int majorinfoid) {
		try {
			return (List<Student>) this.find(
					"from Student where majorinfoid = ? and studentind = 1",
					new Object[] { majorinfoid });
		} catch (DaoException e) {
			log.error("DMS_error:根据专业ID取得该专业学生信息出错！");
			log.error("错误原因:" + e);
		}
		return null;
	}

	/**
	 * 
	 * Description: 返回查询学生的总数
	 * 
	 * @Version1.0 2009-3-30 上午12:36:44 李双江（paradise.lsj@gmail.com）创建
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int getAllStudentsRows() {
		List<Student> lst = this.find("from Student where studentind = 1");
		if (lst != null && lst.size() > 0) {
			return lst.size();
		}
		return 0;
	}

	/**
	 * 
	 * Description: 分页查询所有的学生
	 * 
	 * @Version1.0 2009-3-30 上午12:43:18 李双江（paradise.lsj@gmail.com）创建
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	@SuppressWarnings( { "unchecked" })
	public List<Student> getAllStudentPerPage(int pageSize, int startRow) {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session
						.createQuery("from Student where studentind = 1");
				query.setFirstResult(startRow1);
				query.setMaxResults(pageSize1);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description:根据sql语句高级查询学生信息
	 * 
	 * @Version1.0 2009-3-27 下午10:48:38 李双江（paradise.lsj@gmail.com）创建
	 * @param searchstudentsql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Student> findStudentBySQLSearchConditions(
			String searchstudentsql) {
		return this.getSession().createSQLQuery(searchstudentsql).addEntity(
				Student.class).list();
	}

	/**
	 * 
	 * Description:
	 * 
	 * @Version1.0 Mar 28, 2009 2:28:47 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MajorInfo> findAllMajorInfo() {
		return (List<MajorInfo>) this.find("from MajorInfo");
	}

	/**
	 * 
	 * Description:
	 * 
	 * @Version1.0 Mar 28, 2009 2:28:39 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CollegeInfo> findAllCollegeInfo() {
		return (List<CollegeInfo>) this.find("from CollegeInfo");
	}

	/**
	 * 
	 * Description: 根据sql查询学院 一般是根据专业id来查询学院
	 * 
	 * @Version1.0 Mar 28, 2009 10:53:02 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @param searchcollegesql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CollegeInfo> findCollegeInfoBySQLSearchConditions(
			String majorinfoid) {
		SQLQuery query = (SQLQuery) this
				.getSession()
				.createSQLQuery(
						"select c.collegeinfoid,c.collegeinfoname from collegeinfo c,majorinfo m where m.collegeinfoid = c.collegeinfoid and m.majorinfoid="
								+ Integer.parseInt(majorinfoid)).addEntity(
						CollegeInfo.class);
		return query.list();
	}

	/**
	 * 
	 * Description: 根据sql查询专业 一般是根据学院id来查询专业
	 * 
	 * @Version1.0 Mar 28, 2009 10:55:09 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @param searchmajorinfosql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MajorInfo> findMajorInfoBySQLSearchConditions(
			String collegeinfoid) {
		return (List<MajorInfo>) this
				.find("from MajorInfo where collegeinfoid="
						+ Integer.parseInt(collegeinfoid));
	}

	/**
	 * 
	 * Description:根据sql语句高级查询学生信息
	 * 
	 * @Version1.0 2009-3-27 下午10:48:38 李双江（paradise.lsj@gmail.com）创建 *
	 * @Version1.0 2009-4-27 下午10:48:38 zx增加了studentind的限制
	 * @param searchstudentsql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List findStudentBySQLSearchConditions(final String searchstudentsql,
			final int pageSize, final int startRow) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				String newSql = "select student.studentid,student.studentno,student.studentname,student.studentgender,";
				newSql += "student.studentclass,collegeinfo.collegeinfoname,majorinfo.majorinfoname,dormitory.dormitorydisplayname,allocationinfo.allocationname,student.studenttelephone ";
				newSql += "from student left join collegeinfo on student.collegeinfoid=collegeinfo.collegeinfoid  ";
				newSql += "left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				newSql += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				newSql += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid ";
				newSql += "left join allocationinfo on allocationinfo.allocationid=student.studentpreasignedid ";
				newSql += "where  student.studentind = 1 and ("
						+ searchstudentsql.substring(searchstudentsql.indexOf("where")+5) + ")";
				String sqltosearch = newSql + " limit " + startRow + ","
						+ pageSize;
				Query query = s.createSQLQuery(sqltosearch);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 分页查询预分配标记的学生总数，返回一个学生的list
	 * 
	 * @Version1.0 2009-4-5 上午02:11:42 李双江（paradise.lsj@gmail.com）创建
	 * @param studentpreallocationid
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Student> getStudentPerPageByPreID(
			final String studentpreallocationid, final int pageSize,
			final int startRow) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				// String sqltosearch = "select * from student where
				// studentpreasignedid=" + studentpreallocationid + " limit " +
				// startRow + "," + pageSize;
				SQLQuery query = (SQLQuery) s
						.createSQLQuery("select student.studentid, student.studentno, student.studentname, student.studentidentitycardno, student.collegeinfoid, student.majorinfoid, student.studentclass, student.studentenrollyear, student.studentgender, student.studentbirthday, student.studentethnic, student.studentpolitics, student.studentbirthplace, student.studentmiddleschool, student.studentsecondlanguage, student.studenttype, student.studentstatusid, student.studentpreasignedid, student.studentpicturepath, student.studentenrolltype, student.studenteducationyears, student.studentadmissiontype, student.studentind, student.studenttelephone, student.studenthomeaddress, student.studentarchivesaddress, student.studentnickname, student.studentpassword, student.studentemail, student.studentmisc, student.studentgraduateschool, student.studentycsj, student.studentgraduatedate, student.studentflfx, student.studentadvisorname, collegeinfo.collegeinfoname, majorinfo.majorinfoname from student left join collegeinfo on student.collegeinfoid=collegeinfo.collegeinfoid left join majorinfo on student.majorinfoid=majorinfo.majorinfoid where student.studentind = 1 and student.studentpreasignedid=? limit ?,?");
				query.setInteger(0, Integer.parseInt(studentpreallocationid));
				query.setInteger(1, startRow);
				query.setInteger(2, pageSize);
				/*
				 * Test返回值
				 */
				// log.info("Test学生Preid返回值====================================");
				// for (int m = 0; m < query.list().size(); m++) {
				// Object[] Obj = (Object[]) query.list().get(m);
				// for (int k = 0; k < Obj.length; k++) {
				// log.info(Obj[k] + "----这个是每项返回的结果！");
				// }
				// }
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 返回总行数，根据sql语句高级查询学生信息
	 * 
	 * @Version1.0 2009-3-28 上午10:57:47 李双江（paradise.lsj@gmail.com）创建
	 * @Version1.1 郑旭
	 * @param searchstudentsql
	 * @return
	 */
	public int findStudentBySQLSearchConditionsRows(
			final String searchstudentsql) {
		System.out.println(searchstudentsql);
		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(org.hibernate.Session s)
							throws HibernateException, SQLException {
						String sqltosearch = "select count(*) from student left join studentbedlink on studentbedlink.studentid = student.studentid left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid where student.studentind = 1 and ("
								+ searchstudentsql.substring(searchstudentsql.indexOf("where")+5) + ")";
						//System.out.println("=============="+sqltosearch);
						BigInteger b = (BigInteger) s.createSQLQuery(
								sqltosearch).uniqueResult();
						// log.info("in dao student.for numbers = " + b);
						return b.intValue();
					}
				});
	}

	/**
	 * 
	 * Description:批量更新出查询学生的预分配id
	 * 
	 * @Version1.0 2009-3-29 下午08:04:15 李双江（paradise.lsj@gmail.com）创建
	 * @param studentid
	 * @return
	 */
	public String UpdateBatchStudentpreassignedID(final String[] studentid,
			final String studentpreassignid) {
		return (String) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				String statusMsg = "";
				int sm = 0;
				SQLQuery query = (SQLQuery) s
						.createSQLQuery("update student s set s.studentpreasignedid=? where s.studentid=?");
				for (int i = 0; i < studentid.length; i++) {
					query.setParameter(0, studentpreassignid);
					query.setParameter(1, studentid[i]);
					sm += query.executeUpdate();
					statusMsg = "更新" + sm + " 条学生预分配状态成功！";
				}
				return statusMsg;
			}
		});
	}

	/**
	 * Description: 根据studentid删除预分配标记
	 * 
	 * @Version1.0 2009-4-5 下午08:19:59 闫克（kurtyan777@gmail.com）创建
	 * @param studentid
	 * @return Boolean
	 */
	public Boolean deleteStudentpreassignedID(final String studentid) {
		return (Boolean) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Boolean doInHibernate(org.hibernate.Session session) {
						SQLQuery query = (SQLQuery) session
								.createSQLQuery("update student s set s.studentpreasignedid=? where s.studentid=?");
						query.setInteger(0, -1);
						query.setInteger(1, Integer.parseInt(studentid));
						if (query.executeUpdate() > 0) {
							log.info("DMS_info：" + "删除一条学生id为" + studentid
									+ "的预分配记录!");
							return true;
						}
						return false;
					}
				});
	}

	/**
	 * 
	 * Description:根据传入的sql更新所有高级查询出的学生的studentpreasignedid
	 * 
	 * @Version1.0 Mar 29, 2009 9:00:44 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param sql
	 * @return 影响的行数
	 */
	public int updateAllStudentPreasignedidAtDAO(final String sql) {
		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(org.hibernate.Session s)
							throws HibernateException, SQLException {
						SQLQuery update = s.createSQLQuery(sql);
						return update.executeUpdate();
					}
				});
	}

	/**
	 * 
	 * Description: 查询某一studentpreasignedid的学生的总行数
	 * 
	 * @Version1.0 2009-4-5 上午02:00:19 李双江（paradise.lsj@gmail.com）创建
	 * @param studentpreallocationid
	 * @return
	 */
	public int getStudentByStudentPreIDRows(String studentpreallocationid) {
		try {
			return this.find("from Student where studentpreasignedid=?",
					new Object[] { Integer.parseInt(studentpreallocationid) })
					.size();
		} catch (DaoException e) {
			log.error("DMS_error:查询某一studentpreasignedid的学生的总行数出错");
			log.error("错误原因:" + e);
			return 0;
		}
	}

	/**
	 * 
	 * Description: 由姓名查询学生
	 * 
	 * @Version1.0 Apr 9, 2009 12:26:28 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @param studentname
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List<Student> searchStudentByStudentName(final String studentname,
			final int pageSize, final int startRow) throws DaoException {
		// return this.find("from Student where studentname like
		// '%"+studentname+"%' and studentind = 1");
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session
						.createQuery("from Student where studentname like '%"
								+ studentname + "%' and studentind = 1");
				query.setFirstResult(startRow);
				query.setMaxResults(pageSize);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据姓名模糊查询到的学生的总行数
	 * 
	 * @Version1.0 Apr 23, 2009 2:03:01 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @param studentname
	 * @return
	 */
	public int searchStudentByStudentNameTotalRows(final String studentname) {
		return this.find(
				"from Student where studentname like '%" + studentname
						+ "%' and studentind = 1").size();
	}

	/**
	 * 
	 * Description: 根据学号查询学生的基本信息（非全部信息，仅给非超级管理员看的信息）
	 * 
	 * @Version1.0 Apr 9, 2009 10:42:33 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param studentid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getStudentBasicInfo(String studentno,int studentdormitorytype) {
				String sql = "select student.studentid,student.studentno,studentname,studentgender,";
				sql += "collegeinfo.collegeinfoname,majorinfo.majorinfoname,studentclass,";
				sql += "dormitory.dormitorydisplayname,studenttelephone,studentpicturepath,studentethnic,";
				sql += "studentbirthplace,studentbirthday,studenttype,studentpreasignedid,studentmisc,studentbedlink.studentbedstartintime,studentbedlink.studentbedendouttime from student ";
				sql += "left join collegeinfo on student.collegeinfoid = collegeinfo.collegeinfoid ";
				sql += "left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				sql += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				sql += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid ";
				sql += "where student.studentno=? ";
				if(studentdormitorytype != -1){
					sql += "and studentdormitorytype=?";
				}
				// log.info("这是有史以来最长的sql之一: " + sql);
				Query query = this.getSession().createSQLQuery(sql);
				query.setString(0, studentno);
				if(studentdormitorytype != -1){
					query.setInteger(1, studentdormitorytype);
				}
				return query.list();
	}


	/**
	 * 
	 * Description:根据studentno查询分页学生的总行数
	 * 
	 * @Version1.0 Apr 11, 2009 1:21:52 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param studentno
	 * @return
	 */
	public int getAllStudentRowsByStudentNo(final String[] studentno) {
		int count = 0;
		Query query = this.getSession().createQuery(
				"from Student where studentno=:studentno");
		for (int i = 0; i < studentno.length; i++) {
			query.setParameter("studentno", studentno[i]);
			if (query.list() != null && query.list().size() != 0) {
				count++;
			}
		}
		return count;
	}

	/**
	 * 
	 * Description:根据studentno数组，批量查询学生list
	 * 
	 * @Version1.0 Apr 11, 2009 1:24:33 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param studentno
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Student> getAllStudentPerPageByStudentNo(
			final String[] studentno, int pageSize, int startRow) {
		final int pageSize1 = pageSize;
		final int startRow1 = startRow;
		final List<Student> ls = new ArrayList<Student>();
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query q = session
						.createQuery("from Student where studentno=:studentno");
				for (int i = 0; i < studentno.length; i++) {
					q.setParameter("studentno", studentno[i]);
					q.setFirstResult(startRow1);
					q.setMaxResults(pageSize1);
					if (q.list() != null && q.list().size() != 0) {
						ls.add((Student) q.list().get(0));
					}
				}
				return ls;
			}
		});
	}

	
	/**
	 * 
	 * Description:根据studentno数组，批量查询学生list
	 * liwei
	 * @param studentid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Student> getAllStudentByStudentId(
			final String[] studentid) {
		final List<Student> ls = new ArrayList<Student>();
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query q = session
						.createQuery("from Student where studentid=:studentids");
				for (int i = 1; i < studentid.length; i++) {
					q.setParameter("studentids", Integer.parseInt(studentid[i]));
					System.out.println(studentid[i]);
					if (q.list() != null && q.list().size() != 0) {
						ls.add((Student) q.list().get(0));
					}
				}
				return ls;
			}
		});
	}
	@SuppressWarnings("unchecked")
	public List studentInfoForExportToExcel(final String stringOfStuIdArray) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				/*
				String sql = "select student.studentid,student.studentno,studentname,studentgender,";
				sql += "collegeinfo.collegeinfoname,majorinfo.majorinfoname,studentclass,";
				sql += "dormitory.dormitorydisplayname,studenttelephone,studentpicturepath,studentethnic,";
				sql += "studentbirthplace,studentbirthday,studenttype from student ";
				sql += "left join collegeinfo on student.collegeinfoid = collegeinfo.collegeinfoid ";
				sql += "left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				sql += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				sql += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid ";
				sql += "where student.studentid in (" + stringOfStuIdArray
						+ ")";
				// log.info("in dao student..");
				Query query = s.createSQLQuery(sql);
				return query.list();
				*/
				String sql = "select s.studentno ,s.studentname ,s.studentenrollyear ,"
					+ "s.studenteducationyears ,"
					+ "s.studentidentitycardno ,"
					+ "c.collegeinfoname ,m.majorinfoname ,"
					+ "s.studentclass ,"
					+ "s.studentgender ,s.studentbirthday ,s.studentethnic ,s.studentpolitics ,"
					+ "s.studentbirthplace ,s.studentmiddleschool ,s.studentsecondlanguage,"
					+ "s.studenttype ,s.studentmisc "
					+ "from student s left join collegeinfo c on  s.collegeinfoid = c.collegeinfoid left join majorinfo m on m.majorinfoid = s.majorinfoid where studentid in " 
					+ "(" + stringOfStuIdArray+ ")";
				System.out.println("＝＝＝＝＝＝＝＝＝"+ stringOfStuIdArray);
				System.out.println("------"+ sql);
				SQLQuery q = s.createSQLQuery(sql);
				return q.list();
			}
		});
	}
	/**
	 * 
	 * Description: 导出学生信息到excel，包含学生住宿信息
	 * @Version1.0 Aug 22, 2009 5:15:40 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param stringOfStuIdArray
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List studentInfoForExportToExcel2(final String stringOfStuIdArray) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				String newSql = "select student.studentno,student.studentname,student.studentgender,studentethnic,";
				newSql += "collegeinfo.collegeinfoname,majorinfo.majorinfoname,student.studentclass,dormitory.dormitorydisplayname,student.studentmisc ";
				newSql += "from student left join collegeinfo on student.collegeinfoid=collegeinfo.collegeinfoid  ";
				newSql += "left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				newSql += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				newSql += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid ";
				//newSql += "left join allocationinfo on allocationinfo.allocationid=student.studentpreasignedid ";
				newSql += "where  student.studentind = 1 and student.studentid in " 
					+ "(" + stringOfStuIdArray+ ")";
				SQLQuery q = s.createSQLQuery(newSql);
				return q.list();
			}
		});
	}

	@SuppressWarnings("unchecked")
	public List getStudentHistoryAccomodationInfo(final String studentno,int studentdormitorytype) {
				String sql = "select accomodationhistory.accomodationstartintime,accomodationhistory.accomodationendouttime,dormitory.dormitorydisplayname ";
				sql += "from accomodationhistory ";
				sql += "left join student on student.studentid=accomodationhistory.studentid ";
				sql += "left join dormitory on accomodationhistory.dormitoryid =dormitory.dormitoryid  ";
				sql += "where student.studentno = ? ";
				if(studentdormitorytype != -1){
					sql += "and student.studentdormitorytype=?";
				}
				Query query = this.getSession().createSQLQuery(sql);
				query.setString(0, studentno);
				if(studentdormitorytype != -1){
					query.setInteger(1, studentdormitorytype);
				}
				
				return query.list();
	}

	/**
	 * 
	 * Description: 通过模糊或不全的学号进行查询
	 * 
	 * @Version1.0 Apr 20, 2009 11:55:42 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param dimno
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Student> searchStudentByStudentDimNo(final String dimno,
			final int pageSize, final int startRow) {
		// return this.find("from Student where studentno like '%"+dimno+"%' and
		// studentind = 1");
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			@SuppressWarnings("unchecked")
			public List doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session
						.createQuery("from Student where studentno like '%"
								+ dimno + "%' and studentind = 1");
				query.setFirstResult(startRow);
				query.setMaxResults(pageSize);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据学号模糊查询学生得到的总行数
	 * 
	 * @Version1.0 Apr 23, 2009 1:47:05 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @param dimno
	 * @param pageSize
	 * @param startRow
	 * @return
	 */
	public int searchStudentByStudentDimNoTotalRows(String dimno) {
		return this.find(
				"from Student where studentno like '%" + dimno
						+ "%' and studentind = 1").size();
	}

	/**
	 * 
	 * Description: 高级查询页面全选所有查询到的学生
	 * 
	 * @Version1.0 Apr 21, 2009 4:08:42 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param searchstudentsql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getStudentBySQL(final String searchstudentsql) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				String sqltosearch = "select student.studentid from student ";
				sqltosearch += " left join collegeinfo on student.collegeinfoid=collegeinfo.collegeinfoid";
				sqltosearch += " left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				sqltosearch += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				sqltosearch += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid";
				sqltosearch += " where student.studentind = 1 and  ("
						+ searchstudentsql.substring(searchstudentsql.indexOf("where")+5) + ")";
				// log.info(newSql + "@@newsql");
				// log.info("in dao student..");
				Query query = s.createSQLQuery(sqltosearch);
				return query.list();
			}
		});
	}

	
	/**
	 * 
	 * Description: 高级查询页面全选所有查询到的学生的姓名与电话信息
	 * 
	 * @Version1.0 Apr 21, 2009 4:08:42 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param searchstudentsql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getStudentTelBySQL(final String searchstudentsql) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session s)
					throws HibernateException, SQLException {
				String sqltosearch = "select student.studentid ,student.studentname ,student.studenttelephone  from student ";
				sqltosearch += " left join collegeinfo on student.collegeinfoid=collegeinfo.collegeinfoid";
				sqltosearch += " left join majorinfo on student.majorinfoid = majorinfo.majorinfoid ";
				sqltosearch += "left join studentbedlink on studentbedlink.studentid = student.studentid ";
				sqltosearch += "left join dormitory on studentbedlink.dormitoryid=dormitory.dormitoryid";
				sqltosearch += " where student.studentind = 1 and  ("
						+ searchstudentsql.substring(searchstudentsql.indexOf("where")+5) + ")";
				Query query = s.createSQLQuery(sqltosearch);
				return query.list();
			}
		});
	}

	/**
	 * 
	 * Description: 根据学号获得学生的交费记录
	 * 
	 * @Version1.0 Apr 22, 2009 12:58:29 AM 郑旭（zhengxu2006@gmail.com）创建
	 * @param studentno
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getStudentChargeRecord(String studentno,int studentdormitorytype) {
				String sqltosearch = "select dormitorycharge.dormitorychargeid, dormitorycharge.studentid, dormitorycharge.dormitorychargeyear, dormitorycharge.dormitorychargefees, dormitorycharge.dormitorychargestatus, dormitorycharge.isextension from student,dormitorycharge where student.studentid = dormitorycharge.studentid and student.studentind = 1 and  student.studentno = ? ";
				if(studentdormitorytype != -1){
					sqltosearch +=" and student.studentdormitorytype=?"  ;
				}
				
				// log.info(newSql + "@@newsql");
				// log.info("in dao student..");
				Query query = this.getSession().createSQLQuery(sqltosearch);
				query.setString(0, studentno);
				if(studentdormitorytype != -1){
					query.setInteger(1, studentdormitorytype);
				}
				return query.list();
			
	}

	/**
	 * 
	 * Description: 根据某一条件查询某一个或某一批学生是否存在 ,返回人数(增加学生时验证输入的学号是否存在时用到)
	 * 
	 * @Version1.0 Apr 27, 2009 10:04:49 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param arg
	 * @param field
	 * @return
	 */
	public int isExistStu(final String arg, final String field) {
		String trueField = "";
		int count = 0;
		if ("sno".equals(field)) {
			trueField = "studentno";
			count = this.find(
					"from Student where studentind = 1 and " + trueField
							+ " = '" + arg + "'").size();
		}
		return count;
	}

	/**
	 * 
	 * Description: 返回满足特定studentpreallocationid的studentid列表
	 * 
	 * @Version1.0 May 20, 2009 10:11:23 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param studentpreallocationid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String[][] getStudentIDListByStudentPreID(
			String studentpreallocationid) {
		SQLQuery query = (SQLQuery) this
				.getSession()
				.createSQLQuery(
						"select studentid,studentname,studentenrollyear,studenteducationyears from student where studentind=1 and studentpreasignedid=?");
		query.setInteger(0, Integer.parseInt(studentpreallocationid));

		List list = query.list();
		if (list != null && list.size() > 0) {
			String[][] str = new String[list.size()][4];
			for (int i = 0; i < list.size(); i++) {
				Object[] o = (Object[]) list.get(i);
				str[i][0] = o[0] + "";
				str[i][1] = o[1] + "";
				str[i][2] = o[2] + "";
				str[i][3] = o[3] + "";

			}
			return str;
		}
		return null;
	}

	/**
	 * 
	 * Description:根据表名,字段名,值 判断是否存在 excel上传学生信息,查学院和专业代码(id)是否正确时用到
	 * 
	 * @Version1.0 May 22, 2009 4:23:02 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param table
	 * @param field
	 * @param value
	 * @return
	 */
	public boolean isExist(String table, String field, String value) {
		SQLQuery q = this.getSession().createSQLQuery(
				"select count(*) from " + table + " where " + field + " = '"
						+ value + "'");
		int count = ((Number) q.uniqueResult()).intValue();
		return count > 0;
	}

	public List exportToExcelByStudentid(String ids) {

		return null;

	}
	
	
	/**
	 * 
	 * Description: 编辑学生的备注，在学生基本信息页面用到
	 * @Version1.0 Jul 18, 2009 11:05:06 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param msc
	 * @param stuno
	 * @return
	 */
	public boolean editStuMsc(String msc,String stuno){
		SQLQuery query = (SQLQuery) this.getSession()
			.createSQLQuery("update student s set s.studentmisc=? where s.studentno=?");
		query.setString(0,msc);
		query.setString(1,stuno);
		if (query.executeUpdate() > 0) {
			log.info("DMS_info：" + "更新了一个学生的备注，学号为" + stuno);
			return true;
		}
		else return false;
	}
	/**
	 * 
	 * Description: obj＝{新学号，姓名，学院，老学号，考试方式}
	 * @Version1.0 Aug 25, 2009 3:35:03 PM 郑旭（zhengxu2006@gmail.com）创建
	 * @param obj
	 * @return 
	 */
	public String  getxinStudentnoByStudentno(Object[] obj){
		
		
		
	
			//log.info((Integer)(list.get(0)));
			System.out.println(obj[1].toString());
			
			SQLQuery query = (SQLQuery) this.getSession()
				.createSQLQuery("update student set  studentno='"
						+obj[0]+"' where studentno='"
						+obj[3]+"'");
			if (query.executeUpdate() > 0) {
				//log.info("DMS_info：" + "通过批量更新，修改了一个学生的信息，学号为" + obj[0].toString());
				return "ok";
			}else{
				return "旧学号不存在";
			}
		
		
	}
	/*
	public String  updateStudentCollegeinfoByStudentno(Object[] obj){
		
		
		
		//log.info((Integer)(list.get(0)));
		//System.out.println(obj[1].toString());
		
//		SQLQuery query = (SQLQuery) this.getSession()
//			.createSQLQuery("update student set  collegeinfoid="
//					+obj[2]+" where studentno='"
//					+obj[0]+"'");
//		if (query.executeUpdate() > 0) {
//			//log.info("DMS_info：" + "通过批量更新，修改了一个学生的信息，学号为" + obj[0].toString());
//			return "ok";
//		}else{
//			return "学院不存在";
//		}
		return "..";
	
	
}
*/
	public int getCollegeIdByName(String name){
		try {
			List l =  this.find("from CollegeInfo where collegeinfoname = ? ",new Object[] {name});
			if(null != l && l.size() > 0){
				CollegeInfo college = (CollegeInfo)(l.get(0));
				return college.getCollegeinfoid();
			}else return -1;
		} catch (DaoException e) {
			e.printStackTrace();
			return -1;
		}
		
	}
	public String getCollegeNameById(int id){
		try {
			List l =  this.find("from CollegeInfo where collegeinfoid = ? ",new Object[] {id});
			System.out.println(l.size());
			if(null != l && l.size() > 0){
				CollegeInfo college = (CollegeInfo)(l.get(0));
				
				return college.getCollegeinfoname();
			}else return "";
		} catch (DaoException e) {
			e.printStackTrace();
			return "";
		}
		
	}
	public boolean insertCollege(String collegename){
		SQLQuery query = (SQLQuery) this.getSession()
		.createSQLQuery("select distinct MAX(collegeinfoid) from collegeinfo") ;
		int count = ((Number) query.uniqueResult()).intValue();
		query = (SQLQuery) this.getSession()
		.createSQLQuery("insert into collegeinfo values(?,?)") ;
		query.setInteger(0,count+1);
		query.setString(1,collegename);
		if (query.executeUpdate() > 0) {
			log.info("DMS_info：" + "增加 "+collegename+" 成功");
			return true;
		}
		else return false;
	}
	public boolean deleteCollege(int collegeid){
		SQLQuery query = (SQLQuery) this.getSession()
		.createSQLQuery("delete from collegeinfo where collegeinfoid = ?") ;
		query.setInteger(0, collegeid);
		if (query.executeUpdate() > 0) {
			log.info("DMS_info：" + "删除id为"+collegeid+"的学院成功");
			return true;
		}
		else return false;
	}
	public int updateCollege(int collegeid,String collegename){
		SQLQuery query = (SQLQuery) this.getSession()
		.createSQLQuery("update collegeinfo set collegeinfoname=? where collegeinfoid = ?") ;
		query.setString(0, collegename);
		query.setInteger(1, collegeid);
		int n = query.executeUpdate();
		if (n > 0) {
			log.info("DMS_info：" + "更新id为"+collegeid+"的学院成功");
			
		}else{
			log.error("DMS_error:"+ "更新id为"+collegeid+"的学院失败");
		}
		 return n;
	}
	public boolean insertMajor(int collegeid,String majorname){
		SQLQuery query = (SQLQuery) this.getSession()
		.createSQLQuery("select distinct MAX(majorinfoid) from majorinfo") ;
		int count = ((Number) query.uniqueResult()).intValue();
		query = (SQLQuery) this.getSession()
		.createSQLQuery("insert into majorinfo values(?,?,?)") ;
		query.setInteger(0,count+1);
		query.setInteger(1,collegeid);
		query.setString(2,majorname);
		if (query.executeUpdate() > 0) {
			log.info("DMS_info：" + "在学院id"+collegeid+"下插入"+majorname+"专业");
			return true;
		}
		else return false;
		
	}
	public boolean deleteMajor(int majorid){
		SQLQuery query = (SQLQuery) this.getSession()
		.createSQLQuery("delete from majorinfo where majorinfoid = ?") ;
		query.setInteger(0, majorid);
		if (query.executeUpdate() > 0) {
			log.info("DMS_info：" + "删除id为"+majorid+"的专业成功");
			return true;
		}
		else return false;
	}
	public int updateMajor(int majorid,String majorname){
		SQLQuery query = (SQLQuery) this.getSession()
		.createSQLQuery("update majorinfo set majorinfoname=? where majorinfoid = ?") ;
		query.setString(0, majorname);
		query.setInteger(1, majorid);
		int n = query.executeUpdate();
		if (n > 0) {
			log.info("DMS_info：" + "更新id为"+majorid+"的专业成功");
			
		}else{
			log.error("DMS_error:"+ "更新id为"+majorid+"的专业失败");
		}
		 return n;
	}
	
	
	
	
}